# 三方包
import pymysql
# 自建包
from setting import settings


# 进行查询
def fieldSelect(sql: str, fieldName):
    conn = pymysql.connect(host=settings.DB_HOST, port=settings.DB_PORT, user=settings.DB_USER,
                           password=settings.DB_PASSWORD, db=settings.DB_NAME, charset='utf8')
    cursor = conn.cursor()
    try:
        cursor.execute(sql)
        results = cursor.fetchall()  # 执行sql语句，返回sql查询成功的记录数目
        name = []
        num = []
        for row in results:
            name.append(row[0])
            num.append(row[1])
        return {"fieldName": fieldName, "name": name, "num": num}
    except:
        print("error")

    # 关闭连接，游标和连接都要关闭
    cursor.close()
    conn.close()


# 通用查询
def generalSelect(sql: str):
    conn = pymysql.connect(host=settings.DB_HOST, port=settings.DB_PORT, user=settings.DB_USER,
                           password=settings.DB_PASSWORD, db=settings.DB_NAME, charset='utf8')
    cursor = conn.cursor()
    try:
        cursor.execute(sql)
        return cursor.fetchall()  # 执行sql语句，返回sql查询成功的记录数目
    except:
        print("error")

    # 关闭连接，游标和连接都要关闭
    cursor.close()
    conn.close()


# 离散型sql
def scatterSql(fieldName: str, order: str):
    sql = "select `'%s'`,count(`'%s'`) as '%s'_num " \
          "from car_infomation " \
          "GROUP BY `'%s'` " \
          "order by '%s'_num " + order + \
          " limit 0,10;"
    sql = sql.replace("'%s'", fieldName)
    print(sql)
    return sql


# 区间型sql
def sectionLevelSql(fieldName: str, levelName: list, levelNum: list):
    if len(levelName) != len(levelNum):
        return
    levelName = str(levelName)[1:-1].replace("\"", "'")
    levelNum = str(levelNum)[1:-1]

    sql = "select elt(INTERVAL({},{}),{}) " \
          "as {}_level,count(1) " \
          "from car_infomation " \
          "where {} > 0 " \
          "GROUP BY {}_level " \
          "order by INTERVAL({},{});".format(fieldName, levelNum, levelName,
                                              fieldName, fieldName, fieldName,
                                              fieldName, levelNum)
    return sql

#
# filedName = "final_price"
# levelNum = [0, 5, 8, 10, 15, 20, 25, 35, 50, 70, 100, 150, 300]
# levelName = ["5万以下", "5~8万", "8~10万", "10~15万", "15~20万", "20~25万", "25~35万", "35~50万", "50~70万", "70~100万", "100~150万","150~300万", "300万以上"]
#
# result = fieldSelect(sectionLevelSql(filedName, levelName, levelNum))
# result = fieldSelect(scatterSql(fieldName="level"))
# print(result)
